package com.zcah.db.dbsource.impl;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.zcah.db.dbsource.AbstractDbSource;
import com.zcah.db.domain.ColumnProperty;
import com.zcah.db.domain.DbsourceDO;
import com.zcah.db.vo.TableInfo;

/**
 * 1.Oracle JDBC Thin using a ServiceName:
 * jdbc:oracle:thin:@//<host>:<port>/<service_name> Example:
 * jdbc:oracle:thin:@//192.168.2.1:1521/XE 2.Oracle JDBC Thin using an SID:
 * jdbc:oracle:thin:@<host>:<port>:<SID> Example:
 * jdbc:oracle:thin:@192.168.2.1:1521:X01A 3.Oracle JDBC Thin using a TNSName:
 * jdbc:oracle:thin:@<TNSName> Example: jdbc:oracle:thin:@GL
 * 
 * @author ke
 *
 */
public class OracleSource extends AbstractDbSource {

	private static final Logger logger = LoggerFactory.getLogger(OracleSource.class);

	private static final String URL_SERVICE = "jdbc:oracle:thin:@//%s:%s/%s";
	private static final String URL_SID = "jdbc:oracle:thin:@%s:%s:%s";

	// 返回用户能够查看的所有表（不包括系统用户下的表）
	private static final String SQL_SELECT_TABLES = "SELECT A.OWNER, A.TABLE_NAME, B.COMMENTS FROM ALL_TABLES A INNER JOIN ALL_TAB_COMMENTS B"
			+ " ON A.OWNER = B.OWNER AND A.TABLE_NAME = B.TABLE_NAME"
			+ " WHERE A.OWNER NOT IN ('ANONYMOUS', 'EXFSYS','MDDATA', 'MGMT_VIEW', 'OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMATN_SCHEMA','CTXSYS', 'DBSNMP', 'INTERNAL', 'MDSYS', 'MTSSYS', 'LBACSYS', 'ODM', 'SYS', 'SYSMAN', 'SYSTEM', 'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB', 'ORDDATA', 'APPQOSSYS', 'APEX_030200', 'MGMT_VIEW', 'FLOWS_FILES', 'OWBSYS_AUDIT','SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'OWBSYS', 'OWBSYS_AUDIT', 'APEX_040000')";

	// 查找指定表用户，表名，列名，数据类型，列注释
	private static final String SQL_SHOW_COLUMNS = "SELECT A.OWNER, A.TABLE_NAME, A.COLUMN_NAME, A.DATA_TYPE, B.COMMENTS "
			+ "FROM ALL_TAB_COLUMNS A INNER JOIN ALL_COL_COMMENTS B ON A.OWNER = B.OWNER AND A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME WHERE A.TABLE_NAME = '%s' AND A.OWNER = '%s' ORDER BY A.COLUMN_ID";

	public OracleSource(DbsourceDO db) {
		Map<String, String> parameters = initSource(db);
		String serviceType = parameters.get("serviceType");
		if ("SERVICE_NAME".equals(serviceType)) {
			dbUrl = String.format(URL_SERVICE, db.getIp(), db.getPort(), parameters.get("serviceName"));
		} else if ("SID".equals(serviceType)) {
			dbUrl = String.format(URL_SID, db.getIp(), db.getPort(), parameters.get("serviceName"));
		} else {
			throw new IllegalArgumentException("非法serviceType: " + serviceType);
		}
		try {
			connection = DriverManager.getConnection(dbUrl, username, password);
		} catch (SQLException e) {
			errMsg = e.getLocalizedMessage();
		}
	}

	@Override
	public List<TableInfo> getSchemasTables() {
		List<TableInfo> schemaTableInfos = new ArrayList<>();
		try (ResultSet rs = getSqlResultSet(SQL_SELECT_TABLES);) {
			while (rs.next()) {
				TableInfo tableInfo = new TableInfo();
				// oracle 中 schema 即 owner
				tableInfo.setTableName(rs.getString("OWNER") + "." + rs.getString("TABLE_NAME"));
				tableInfo.setComment(rs.getString("COMMENTS"));
				schemaTableInfos.add(tableInfo);
			}
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage(), e);
		}

		return schemaTableInfos;
	}

	@Override
	public List<ColumnProperty> getTableColumns(String fullTableName) {
		String[] fullNames = fullTableName.split("\\.");
		String sql = String.format(SQL_SHOW_COLUMNS, fullNames[1], fullNames[0]);
		List<ColumnProperty> columns = new ArrayList<>();

		try (ResultSet rs = getSqlResultSet(sql)) {
			while (rs.next()) {
				ColumnProperty columnProperty = new ColumnProperty();
				columnProperty.setColumnComment(rs.getString("COMMENTS"));
				columnProperty.setColumnDataType(rs.getString("DATA_TYPE"));
				columnProperty.setColumnName(rs.getString("COLUMN_NAME"));
				columns.add(columnProperty);
			}
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage(), e);
		}
		return columns;
	}

}
